using System;
using System.Data;
using Mono.Data.SqliteClient;
using System.Collections.Generic;
using System.IO;

namespace rebidesCsharp
{

	public class CalculaDados
	{

		public CalculaDados ()
		{
			
		}
		
		public List<int> calculaNDocentesAno()
		{
			Console.WriteLine("Calculando numero de Docentes por Ano...");
			
			List<int> listaEstab = new List<int>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++)
			{
				string sql = "SELECT count(distinct docente) " +
					"FROM fichas_docencia " + 
					"WHERE ano = " + ano;
				
            	dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();	
				
				while(reader.Read())
				{
					Int32 total = reader.GetInt32(0);
					listaEstab.Add(total);
				}
				
				reader.Close();
				reader = null;				
			}
			
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return listaEstab;
		}
		
		public Dictionary<string, Dictionary<string, int>> calculaNDocentesEstabelecimento()
		{
			Dictionary<string, Dictionary<string, int>> dEstabDocentesAno = new Dictionary<string, Dictionary<string, int>>();
			
			Console.WriteLine("Calculando numero de Docentes por Estabelecimento e por Ano...");
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				Dictionary<string, int> estabNDocentes = new Dictionary<string, int>();
				
				string sql = "SELECT e.designacao, count(distinct f.docente)" +
					"FROM fichas_docencia as f " +
					"INNER JOIN estabelecimentos as e ON " +
					"f.estabelecimento = e.designacao " +
					"WHERE Ano = " + ano +
					" GROUP BY e.designacao";
						
				dbcmd.CommandText = sql;
				IDataReader reader = dbcmd.ExecuteReader();
				while(reader.Read()) 
				{
					string nome = reader.GetString (0);
					Int32 total = reader.GetInt32 (1);
					estabNDocentes.Add(nome, total);
				}
					
				reader.Close();
				reader = null;
				
				dEstabDocentesAno.Add("Ano 200" + ano, estabNDocentes);
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return dEstabDocentesAno;
		}
		
		public Dictionary<string, Dictionary<string, int>> calculaNDocentesGrau()
		{
			Dictionary<string, Dictionary<string, int>> dGrauDocentesAno = new Dictionary<string, Dictionary<string, int>>();
			
			Console.WriteLine("Calculando numero de Docentes por Grau e por Ano...");
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				Dictionary<string, int> grauNDocentes = new Dictionary<string, int>();

				string sql = "SELECT grau, count(distinct docente) " +
						"FROM fichas_docencia " +
						"WHERE grau LIKE 'Do%' " +
						"AND ano = " + ano + 
						" UNION " +
						"SELECT grau, count(distinct docente) " +
						"FROM fichas_docencia " +
						"WHERE grau LIKE 'Me%' " +
						"AND ano = " + ano + 
						" UNION " +
						"SELECT grau, count(distinct docente) " +
						"FROM fichas_docencia " +
						"WHERE grau LIKE 'Li%' " +
						"AND ano = " + ano;
				
				dbcmd.CommandText = sql;
				IDataReader reader = dbcmd.ExecuteReader();
				while(reader.Read()) 
				{
					string grau = reader.GetString (0);
					Int32 total = reader.GetInt32(1);
					grauNDocentes.Add(grau, total);
				}
				reader.Close();
				reader = null;
				
				dGrauDocentesAno.Add("Ano 200" + ano, grauNDocentes);
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return dGrauDocentesAno;
		}
		
		public Dictionary<string, Dictionary<string, Dictionary<string, int>>> calculaNDocentesEstabGrau()
		{
			Dictionary<string, Dictionary<string, Dictionary<string, int>>> d = new Dictionary<string, Dictionary<string, Dictionary<string, int>>>();
			
			Console.WriteLine("Calculando numero de Docentes por Estabelecimento, Grau e Ano...");
			
			List<string> graus = new List<string>();
			graus.Add("Li");//licenciatura
			graus.Add("Me");//mestrado
			graus.Add("Do");//doutoramento
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				Dictionary<string, Dictionary<string, int>> dGrauEstabDocente = new Dictionary<string, Dictionary<string, int>>();
				
				foreach(string g in graus)
				{
					
					Dictionary<string, int> dEstabNDocentes = new Dictionary<string, int>();
					
					string sql = "SELECT e.designacao, grau, count(distinct f.docente) " +
							"FROM fichas_docencia as f " +
							"INNER JOIN estabelecimentos as e ON " +
							"f.estabelecimento = e.designacao " + 
							"WHERE Ano = " + ano + 
							" AND grau LIKE '"+g+"%' " +
							"GROUP BY e.designacao";			
					dbcmd.CommandText = sql;
					IDataReader reader = dbcmd.ExecuteReader();
					string grau = "";
					while(reader.Read()) 
					{
						string estab = reader.GetString (0);
						grau = reader.GetString (1);
						Int32 total = reader.GetInt32 (2);
						
						char c = grau[0];			
						string s = c.ToString();
				
						if(s.Equals("D")) grau = "Doutoramento";
						else if(s.Equals("M")) grau = "Mestrado";
						else if(s.Equals("L")) grau = "Licenciatura";
						
						dEstabNDocentes.Add(estab, total);
					}
					
					dGrauEstabDocente.Add(grau, dEstabNDocentes);
					
					reader.Close();
					reader = null;
				}
				d.Add("Ano 200" + ano, dGrauEstabDocente);
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return d;
		}
		
		public Dictionary<string, Dictionary<string, int>> calculaNDoutoresEstab()
		{
			Console.WriteLine("Calculando numero de Docentes com Doutoramento por Estabelecimento e por Ano...");
			
			Dictionary<string, Dictionary<string, int>> d = new Dictionary<string, Dictionary<string, int>>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				Dictionary<string, int> estabNDocentes = new Dictionary<string, int>();
				
				string sql = "SELECT e.designacao, count(distinct f.docente) " +
						"FROM fichas_docencia as f " +
						"INNER JOIN estabelecimentos as e ON " +
						"f.estabelecimento = e.designacao " + 
						"WHERE Ano = " + ano + 
						" AND grau LIKE 'Do%' " +
						"GROUP BY e.designacao";		
				dbcmd.CommandText = sql;
				IDataReader reader = dbcmd.ExecuteReader();
				while(reader.Read()) 
				{
					string estab = reader.GetString (0);
					Int32 total = reader.GetInt32 (1);
					estabNDocentes.Add(estab, total);
				}
				d.Add("Ano 200" + ano, estabNDocentes);
				reader.Close();
				reader = null;
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return d;
		}
		
		public Dictionary<string, Dictionary<string, List<string>>> calculaDoutoresEstab()
		{
			Console.WriteLine("Calculando os Docentes com Doutoramento por Estabelecimento e por Ano...");
			
			Dictionary<string, Dictionary<string, List<string>>> d = new Dictionary<string, Dictionary<string, List<string>>>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();

			for(int ano = 0; ano < 10; ano ++ )
			{
				Console.WriteLine("ANO == " + ano);
				List<string> listaEstabAno = getlistEstabAno(ano);
				Dictionary<string, List<string>> estabDocentes = new Dictionary<string, List<string>>();

				foreach(string estab in listaEstabAno)
				{
					List<string> listaDocentes = new List<string>();
					
					string sql = "SELECT distinct docente " +
					"FROM fichas_docencia " +
					"WHERE Ano = " + ano +
					" AND estabelecimento LIKE '"+ estab +"'";
					Console.WriteLine(sql);
					
					dbcmd.CommandText = sql;
					IDataReader reader = dbcmd.ExecuteReader();
					while(reader.Read())
					{
						string docente = reader.GetString (0);
						Console.WriteLine(docente);
						listaDocentes.Add(docente);
					}
				
					estabDocentes.Add(estab, listaDocentes);
					reader.Close();
					reader = null;
				}
				d.Add("Ano 200" + ano, estabDocentes);
			}
			
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return d;
		}		
		
		public List<string> getlistEstabAno(int ano){
			List<string> listaEstabelecimento = new List<string>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
				
			string sql = "SELECT distinct estabelecimento " +
				"FROM fichas_docencia " +
				"WHERE Ano = " + ano;
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader();
			while(reader.Read())
			{
				string estab = reader.GetString (0);
				listaEstabelecimento.Add(estab);
			}
				
			reader.Close();
			reader = null;
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return listaEstabelecimento;
		}
		
		public void calculaConjuntoDocentesMudaramEstab(StreamWriter f)
		{
			Console.WriteLine("Calculando o conjunto de docentes que mudaram de Estabelecimentos por Ano...");
			
			int anoAnterior = 0;
			int anoActual = 1;
			
			for(anoActual = 1; anoActual<10; anoActual++)
			{
			
				List<Docente> listaDocentesAnterior = getListaDocentesEstabelecimento(anoAnterior);
				List<Docente> listaDocentesActual = getListaDocentesEstabelecimento(anoActual);
				
				foreach(Docente i in listaDocentesAnterior)
				{
					foreach(Docente j in listaDocentesActual)
					{                                  
						if(i.nome.Equals(j.nome))
						{                                                  
							if(!i.estab.Equals(j.estab))
							{
								Console.WriteLine("Docente: " + i.nome);
								Console.WriteLine("Estabelecimento - Ano " + i.ano + ": " + i.estab);
								Console.WriteLine("Estabelecimento - Ano " + j.ano + ": " + j.estab);
								f.WriteLine();
								f.WriteLine("Docente: " + i.nome);
								f.WriteLine("Estabelecimento - Ano " + i.ano + ": " + i.estab);
								f.WriteLine("Estabelecimento - Ano " + j.ano + ": " + j.estab);
							}
						}
					}
				}
				anoAnterior++;
			}
		}
		
		public List<Docente> getListaDocentesEstabelecimento(int ano)
		{
			List<Docente> listaDocentesEstab = new List<Docente>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			string sql = "SELECT distinct f.docente, e.designacao " +
					"FROM fichas_docencia as f " +
					"INNER JOIN estabelecimentos as e ON " +
					"f.estabelecimento = e.designacao " + 
					"WHERE Ano = " + ano +
					" GROUP BY e.designacao, f.docente " +
					"ORDER BY e.designacao, f.docente";
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader(); 
			while(reader.Read())        
			{
				string docente = reader.GetString (0);
				string estab = reader.GetString (1);
				listaDocentesEstab.Add(new Docente(docente, estab, ano));
			}
			reader.Close();         
			reader = null;            
			dbcmd.Dispose();  
			dbcmd = null;           
			dbcon.Close();        
			dbcon = null;           
			
			return listaDocentesEstab;
		}
		
		
		public Dictionary<string, List<string>> calculaListaEstab()
		{
			Console.WriteLine("Calculando a lista de Estabelecimentos e por Ano...");
			
			Dictionary<string, List<string>> dListaEstabelecimentosAno = new Dictionary<string, List<string>>();

			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				List<string> listaEstab = new List<string>();
				
				string sql = "SELECT distinct estabelecimento, ano " +
					"FROM fichas_docencia " +
					"WHERE Ano = " + ano +
					" ORDER BY estabelecimento";
						
				dbcmd.CommandText = sql;
				IDataReader reader = dbcmd.ExecuteReader();
				while(reader.Read())
				{
					string nome = reader.GetString (0);
					listaEstab.Add(nome);
				}
					
				reader.Close();
				reader = null;
                dListaEstabelecimentosAno.Add("Ano 200" + ano, listaEstab);
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return dListaEstabelecimentosAno;
		}
		
		public Dictionary<string, Dictionary<string, List<string>>> calculaListaDetentoresGrau()
		{
			Console.WriteLine("Calculando a lista de detentores de um Grau por Ano...");
			
			Dictionary<string, Dictionary<string, List<string>>> d = new Dictionary<string, Dictionary<string, List<string>>> ();

			List<string> graus = new List<string>();
			graus.Add("Li");//licenciatura
			graus.Add("Me");//mestrado
			graus.Add("Do");//doutoramento
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			for(int ano = 0; ano < 10; ano ++ )
			{
				Dictionary<string, List<string>> dGrauDocente = new Dictionary<string, List<string>>();
				
				foreach(string g in graus)
				{
					List<string> listaDocentes = new List<string>();
					string sql = "SELECT distinct docente, grau " +
							"FROM fichas_docencia " + 
							"WHERE Ano = " + ano + 
							" AND grau LIKE '"+g+"%' ";			
					dbcmd.CommandText = sql;
					IDataReader reader = dbcmd.ExecuteReader();
					string grau = "";
					while(reader.Read()) 
					{
						string docente = reader.GetString (0);
						grau = reader.GetString (1);
						
						char c = grau[0];			
						string s = c.ToString();
				
						if(s.Equals("D")) grau = "Doutoramento";
						else if(s.Equals("M")) grau = "Mestrado";
						else if(s.Equals("L")) grau = "Licenciatura";
					
						listaDocentes.Add(docente);
					}
					
					dGrauDocente.Add(grau, listaDocentes);
					
					reader.Close();
					reader = null;
				}
				d.Add("Ano 200" + ano, dGrauDocente);
			}
			dbcmd.Dispose();
			dbcmd = null;
			dbcon.Close();
			dbcon = null;
			
			return d;	
		}
		
		public void calculaListaDocentesMudaramInst(StreamWriter f)
		{
			Console.WriteLine("Calculando a lista de docentes que mudaram de Instituição por Ano...");
			
			int anoAnterior = 0;
			int anoActual = 1;
			
			for(anoActual = 1; anoActual<10; anoActual++)
			{
				List<Docente> listaDocentesAnterior = getListaDocentesInst(anoAnterior);
				List<Docente> listaDocentesActual = getListaDocentesInst(anoActual);
				List<string> listaAbandonos = new List<string>();
				
				foreach(Docente i in listaDocentesAnterior)
				{
					foreach(Docente j in listaDocentesActual)
					{                                  
						if(i.nome.Equals(j.nome))
						{                                                  
							if(!i.estab.Equals(j.estab))
							{
								if(!listaAbandonos.Contains(i.nome))
								{
									listaAbandonos.Add(i.nome);
								}
							}
						}
					}
				}				
				
				Console.WriteLine();
				Console.WriteLine("------------ANO 200" + anoAnterior + "------------");
					
				f.WriteLine();
				f.WriteLine("------------ANO 200" + anoAnterior + "------------");
				f.WriteLine();
				
				foreach(string docentes in listaAbandonos)
				{
					Console.WriteLine(docentes);
					f.WriteLine(docentes);
				}
				
				anoAnterior++;
			}	
		}
		
		
		public void calculaListaDocentesNovosInst(StreamWriter f)
		{
			Console.WriteLine("Calculando a lista de docentes Novos na Instituição por Ano...");
			
			int anoAnterior = 0;
			int anoActual = 1;
			
			for(anoActual = 1; anoActual<10; anoActual++)
			{
				List<Docente> listaDocentesAnterior = getListaDocentesInst(anoAnterior);
				List<Docente> listaDocentesActual = getListaDocentesInst(anoActual);
				List<string> listaNovosDocentes = new List<string>();
				
				foreach(Docente i in listaDocentesAnterior)
				{
					foreach(Docente j in listaDocentesActual)
					{                                  
						if(i.nome.Equals(j.nome))
						{                                                  
							if(!i.estab.Equals(j.estab))
							{
								if(!listaNovosDocentes.Contains(i.nome))
								{
									listaNovosDocentes.Add(i.nome);
								}
							}
						}
					}
				}				
				
				Console.WriteLine();
				Console.WriteLine("------------ANO 200" + anoActual + "------------");
				
				f.WriteLine();
				f.WriteLine("------------ANO 200" + anoActual + "------------");
				f.WriteLine();
				
				foreach(string docentes in listaNovosDocentes)
				{
					Console.WriteLine(docentes);
					f.WriteLine(docentes);
				}
				
				anoAnterior++;
			}	
		}
		
		public List<Docente> getListaDocentesInst(int ano)
		{
			List<Docente> listaDocentesInst = new List<Docente>();
			
			string connectionString = "URI=file:rebides.db, version=3";
			IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(connectionString);
			dbcon.Open();
			IDbCommand dbcmd = dbcon.CreateCommand();
			
			string sql = "SELECT distinct f.docente, t.designacao " +
					"FROM fichas_docencia as f " +
					"INNER JOIN tipos_estabelecimento as t ON " +
					"f.tipo_estabelecimento = t.designacao " + 
					"WHERE Ano = " + ano +
					" GROUP BY t.designacao, f.docente " +
					"ORDER BY t.designacao, f.docente";
					
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader(); 
			while(reader.Read())        
			{
				string docente = reader.GetString (0);
				string inst = reader.GetString (1);
				listaDocentesInst.Add(new Docente(docente, inst, ano));
			}
			reader.Close();         
			reader = null;            
			dbcmd.Dispose();  
			dbcmd = null;           
			dbcon.Close();        
			dbcon = null;           
			
			return listaDocentesInst;
		}
		
		public void calculaNDocentesMudaramEstab(StreamWriter f)
		{
			Console.WriteLine("Calculando o numero de docentes que mudaram de Estabelecimentos...");
			
			int anoAnterior = 0;
			int anoActual = 1;
			
			for(anoActual = 1; anoActual<10; anoActual++)
			{
				List<Docente> listaDocentesAnterior = getListaDocentesEstabelecimento(anoAnterior);
				List<Docente> listaDocentesActual = getListaDocentesEstabelecimento(anoActual);
				
				int contador = 0;
				
				foreach(Docente i in listaDocentesAnterior)
				{
					foreach(Docente j in listaDocentesActual)
					{                      
						if(i.nome.Equals(j.nome))
						{                                                  
							if(!i.estab.Equals(j.estab))
							{
								contador++;
							}
						}
					}
				}
				anoAnterior++;
				Console.WriteLine("ANO 200" + anoAnterior);
				Console.WriteLine("Numero de migrações = " + contador);
				
				f.WriteLine("ANO 200" + anoAnterior);
				f.WriteLine("Numero de migrações = " + contador);
				f.WriteLine();
			}
		}
			
		
	}//fim class CalculaDados
	
}//fim namespace
